/**
 * Created by jackliu on 15/12/16.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');


// Schema 结构
var ItbUserModel = function(obj){
    ItbBaseModel.call(this,obj);

  // 以下为表定义数据，需要的字段就填入
  //`user_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  //`user_name` varchar(50) NOT NULL DEFAULT '' COMMENT '账号',
  //`password` varchar(50) NOT NULL DEFAULT '' COMMENT 'MD5密码',
  //`nickname` varchar(50) DEFAULT NULL COMMENT '昵称',
  //`user_type` int(1) NOT NULL DEFAULT '1' COMMENT '0后台；1用户',
  //`wechat_token` varchar(100) DEFAULT NULL COMMENT '微信',
  //`mobile` bigint(20) DEFAULT NULL COMMENT '手机号',
  //`email` varchar(100) DEFAULT NULL COMMENT 'E-mail',
  // `uuid` varchar(100) DEFAULT NULL COMMENT 'ios uuid',
  //`create_time` datetime NOT NULL,
  //`update_time` datetime NOT NULL,
  //`del_flg` int(1) NOT NULL DEFAULT '0',


};
ItbUserModel.prototype = new ItbBaseModel();

// 取得表名
ItbUserModel.prototype.getTableName = function(){
    return 'itb_user';
}
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
ItbUserModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'user_id='+this.user_id;
}
// 自定义查询列子
ItbUserModel.prototype.updatePasssword = function(callback){
    var THIS = this;
    var tableName = this.getTableName();
    //mongodb更新s
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = 'UPDATE '+tableName+' SET password=? WHERE del_flg=0 AND mobile=? AND user_type=1 ';
        var sqlValue = [THIS.password, THIS.mobile];
        var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error) {
                callback(err);
            } else {
                if (result.affectedRows == 1) {
                    callback(null);
                } else {
                    callback(1);// err
                }
            }
        });
    });
}

// 根据uuid获取临时用户id
ItbUserModel.prototype.getTempUserByUUID = function(callback){
    var THIS = this;

    pool.pool.getConnection(function(err, connection) {

        var sql = " SELECT * FROM ITB_USER WHERE uuid=? AND user_type=1 AND del_flg=0 AND mobile IS NULL ORDER BY user_id ";
        var param = [THIS.uuid];
        var exeSql = mysql.format(sql, param);
        console.log(exeSql);
        connection.query(exeSql, function (error, results) {
            connection.release();
            console.log();

            callback(error, results);
        });
    });
}

// 根据用户id更新用户信息
ItbUserModel.prototype.updateInfoById = function(callback){
    var THIS = this;

    pool.pool.getConnection(function(err, connection) {

        var sql = " UPDATE ITB_USER SET user_name=?, user_type=?, password=?, mobile=?, uuid=?, update_time=sysdate() " +
            " WHERE user_id=? AND del_flg=0 ";
        var param = [THIS.user_name, THIS.user_type, THIS.password, THIS.mobile, THIS.uuid, THIS.user_id];
        var exeSql = mysql.format(sql, param);
        console.log(exeSql);
        connection.query(exeSql, function (error, result) {
            if (error) {
                console.log('[ItbUserModel.js] [updateInfoById] Update Error: ' + error);
            }
            connection.release();

            callback(error, result);
        });
    });
}


// 根据用户id更新用户信息
ItbUserModel.prototype.findByUnionIdOrOpenId = function(callback){
    var THIS = this;

    pool.pool.getConnection(function(err, connection) {

        var sql = " SELECT * FROM ITB_USER WHERE del_flg=0 "+
            "and (wechat_token='"+THIS.wechat_token+"' "+
            "or wechat_token_union='"+THIS.wechat_token_union+"') ";
        var param = [THIS.uuid];
        var exeSql = mysql.format(sql, param);
        console.log(exeSql);
        connection.query(exeSql, function (error, results) {
            connection.release();
            console.log();

            callback(error, results);
        });
    });
}

// 获取用户信息（包括全部平台信息）
ItbUserModel.prototype.findWithPlatformInfo = function(callback){
    var THIS = this;

    pool.pool.getConnection(function(err, connection) {

        // 搜索条件
        var search_config = "";
        if(THIS.user_id){
            search_config += " AND t.user_id="+THIS.user_id;
        }
        if(THIS.mobile) {
            search_config += " AND t.mobile="+THIS.mobile;
        }

        if (THIS.platform || THIS.field1 || THIS.field2
            || THIS.field3 || THIS.field4 || THIS.field5) {

            search_config += " AND t.user_id IN ("+
                " SELECT DISTINCT p2.user_id FROM itb_platform_user p2 "+
                " WHERE p2.del_flg=0 ";

            if(THIS.platform) {
                search_config += " AND p2.platform="+THIS.platform;
            }
            if(THIS.field1){
                search_config += " AND p2.field1='"+THIS.field1+"'";
            }
            if(THIS.field2){
                search_config += " AND p2.field2='"+THIS.field2+"'";
            }
            if(THIS.field3){
                search_config += " AND p2.field3='"+THIS.field3+"'";
            }
            if(THIS.field4){
                search_config += " AND p2.field4='"+THIS.field4+"'";
            }
            if(THIS.field5){
                search_config += " AND p2.field5='"+THIS.field5+"'";
            }
            search_config += " ) "
        }

        // 查询微信的sql
        var sql0 =  ' SELECT t.*, '+
                    '       1 AS platform, '+
                    '       t.wechat_nickname AS p_nickname, '+
                    '       t.head_url AS p_head_url, '+
                    '       t.wechat_token_union AS field1, '+
                    '       t.wechat_token AS field2, '+
                    '       null as field3, '+
                    '       null as field4, '+
                    '       null as field5 '+
                    ' FROM itb_user t '+
                    ' WHERE t.del_flg=0  AND t.user_type = 1'+
                    ' AND (t.wechat_token_union is not null OR t.wechat_token is not null) ';

        // 查询小米等其它第三方账号的sql
        var sql1 =   " SELECT t.*, p.platform, p.nickname AS p_nickname, p.head_url AS p_head_url, "+
                    " p.field1, p.field2, p.field3, p.field4, p.field5 "+
                    " FROM itb_user t, itb_platform_user p "+
                    " WHERE t.del_flg=0 AND p.del_flg=0 AND t.user_id=p.user_id AND t.user_type = 1";




        // 总sql
        var sql = " ( "+sql0+search_config+" ) union all ( " +sql1+search_config+" ) ";
        sql += " ORDER BY update_time DESC, platform ASC ";

        //var param = [THIS.mobile];
        //var exesql = mysql.format(sql, param);
        console.log(sql);
        connection.query(sql, function (error, results) {
            connection.release();
            console.log();

            callback(error, results);
        });
    });
};


ItbUserModel.prototype.getUserInfoWithOrgRole = function(userId, orgId, callback) {
    pool.pool.getConnection(function(err, connection) {

        if(err) {
            console.log("ItbUserModel [getUserInfoWithOrgRole] getConnection err:")
            return callback(err,null);
        }

        var sql =
            " SELECT t.*, IFNULL(r.role,0) AS role " +
            " FROM itb_user t " +
            " LEFT JOIN tg_org_r r ON r.del_flg=0 AND t.user_id=r.user_id AND r.org_id=" + orgId+
            " WHERE t.del_flg=0 AND t.user_id=" + userId;

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            return callback(error, result);
        });
    });
};

// 获取指定角色用户
ItbUserModel.prototype.getOrgRoleUser = function(orgId, role, callback) {
    pool.pool.getConnection(function(err, connection) {

        if(err) {
            console.log("ItbUserModel [getOrgRoleUser] getConnection err:")
            return callback(err,null);
        }

        var sql =
            " SELECT t.*, IFNULL(r.role,0) AS role " +
            " FROM itb_user t, tg_org_r r " +
            " WHERE t.del_flg=0 "+
            " AND r.del_flg=0 AND t.user_id=r.user_id " +
            " AND r.org_id=" + orgId +
            " AND (r.role & "+Number(role||'0') + ")="+Number(role||'0')+
            " ORDER BY t.user_id ";

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            return callback(error, result);
        });
    });
};

// 根据手机号获取组织用户
ItbUserModel.prototype.getOrgUserByMobile = function(mobile, orgType, callback) {
    pool.pool.getConnection(function(err, connection) {

        if(err) {
            console.log("ItbUserModel [getOrgUserByMobile] getConnection err:");
            return callback(err,null);
        }

        var sql =
            " SELECT t.*, o.org_id, IFNULL(r.role,0) AS role " +
            " FROM itb_user t, tg_org o, tg_org_r r " +
            " WHERE t.del_flg=0 AND t.mobile="+mobile+
            " AND r.del_flg=0 AND t.user_id=r.user_id AND r.state = 10 " +
            " AND o.del_flg=0 AND o.state=4 AND o.org_id=r.org_id ";
        if(orgType>0) {
            sql += " AND o.type="+orgType;
        }
        sql += " ORDER BY o.org_id, t.user_id ";

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            return callback(error, result);
        });
    });
};

// todo 新增接口, 待审核 begin
ItbUserModel.prototype.findBriefUserInfo = function(userId, callback) {
    pool.pool.getConnection(function(err, connection) {

        if(err) {
            console.log("ItbUserModel [findBriefUserInfo] getConnection err:")
            return callback(err,null);
        }

        var sql =
            " SELECT t.mobile AS mobile, " +
        "        t.uuid AS registerUUID, " +
        "        t.nickname AS nickName, " +
        "        t.head_url AS headUrl, " +
        "        t.user_type AS userType, " +
        "        t.user_id AS userId " +
        " FROM itb_user t " +
        " WHERE del_flg=0 " +
        "   AND user_id = " + userId;

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error){
                return callback(error, null);
            }
            if(!result || result.length==0){
                return callback(null,null);
            }

            return callback(null, result[0]);
        });
    });
};


// todo 新增接口, 待审核 end

// 严格插入（手机号不能重复）
ItbUserModel.prototype.insWhenMobileNotExist = function(callback){
    var THIS = this;
    var tableName = this.getTableName();
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = ' INSERT INTO itb_user (password,uuid,user_type,mobile,create_time,update_time,update_by) ';
        sql += ' SELECT "'+(THIS.password||'e10adc3949ba59abbe56e057f20f883e')+'"'
            + ', "'+(THIS.uuid||'')+'"'
            + ', '+(THIS.user_type)
            + ', '+(THIS.mobile)
            + ', sysdate(), sysdate(), "API_INS" '
            + ' FROM dual WHERE not exists '
            +' (select user_id from itb_user where del_flg=0 and mobile='+THIS.mobile+') ';

        console.log(sql);
        connection.query(sql, function(err, result) {
            // And done with the connection.
            connection.release();

            if(err) {
                console.log(err);
                callback(err, null);
            } else {
                callback(null, result.insertId||null);
            }
        });
    });
};


/**
 * 获取后台用户信息
 *
 * @author 王俊
 * @date 2018-04-12
 *
 * @param {Object} options 查询参数
 * @param {Number} options.queryType 查询方式, 方法内约定, 1 标记 账户+密码查询, 2 标记 UserId 查询, 3 标记仅账户查询
 * @param {Number} [options.userId]  用户唯一标识, options.queryType 为 2 时, 必传
 * @param {String} [options.userName] 用户名, options.queryType 为 1/3时, 必传
 * @param {String} [options.password] 用密码, options.queryType 为 1 时, 必传
 * @param {Function} callback 查询回调
 */
ItbUserModel.prototype.getBackUserInfo = function (options, callback) {
    pool.pool.getConnection(function(getConErr, connection) {
        if(getConErr) {
            console.log('ItbUserModel.prototype.getBackUserInfo getConErr: ' + getConErr);
            return callback(getConErr, null);
        }

        var sql =
            ' SELECT iu.user_id AS userId, ' +
            '        iu.nickname AS nickName, ' +
            '        iu.user_name AS userName, ' +
            '        iu.head_url AS headUrl, ' +
            '        iu.signature, ' +
            '        iu.user_type AS userType, ' +
            '        tor.org_id AS orgId, ' +
            '        to.name AS orgName, ' +
            '        tor.role, ' +
            '        to.type AS orgType ' +
            ' FROM itbour.itb_user iu ' +
            ' LEFT JOIN itbour.tg_org_r tor ON tor.user_id = iu.user_id AND tor.del_flg = 0 AND tor.state = 10 ' +
            ' LEFT JOIN itbour.tg_org`to` ON to.org_id = tor.org_id AND to.state = 4  AND to.del_flg = 0 AND iu.user_id = tor.user_id ' +
            ' WHERE iu.del_flg = 0 ' +
            '   AND iu.user_type = 0 ' ;

        if(options.queryType === 1) {
            sql += '   AND iu.user_name = "' + options.userName + '" ';
            sql += '   AND iu.password = "' + options.password + '" ';
        } else if(options.queryType === 2) {
            sql += '   AND iu.user_id = ' + options.userId;
        } else if(options.queryType === 3) {
            sql += '   AND iu.user_name = "' + options.userName + '" ';
        } else {
            return callback('查询参数错误', null);
        }
        console.log(sql);
        connection.query(sql, function(queryErr, results) {
            // And done with the connection.
            connection.release();
            if(queryErr || !results || results.length <=0) {
                console.log('ItbUserModel.prototype.getBackUserInfo queryErr: ' + queryErr);
                return callback(queryErr, null);
            }
            // 查询到了数据, 重组数据结构
            var userInfo = {
                userId : results[0].userId,
                userName : results[0].userName,
                nickName : results[0].nickName,
                headUrl : results[0].headUrl,
                userType : results[0].userType,
                itbourBackInfo : null,
                yjxAgentInfo : null,
                yjscDesignPlatformInfo : null,
                yjscPrintersInfo : null
            };

            for(var i=0; i<results.length; i++) {
                var info = results[i];

                var orgInfo = {
                    orgId : info.orgId,
                    orgName : info.orgName,
                    orgType : info.orgType,
                    role : info.role
                };
                // 组织类型: 100示界港; 200一键秀VIP代理商; 300一键生成入驻设计公司; 400印刷厂
                if (orgInfo.orgType === 100) {
                    userInfo.itbourBackInfo = orgInfo;
                } else if(orgInfo.orgType === 200) {
                    userInfo.yjxAgentInfo = orgInfo;
                } else if(orgInfo.orgType === 300) {
                    userInfo.yjscDesignPlatformInfo = orgInfo;
                } else if(orgInfo.orgType === 400) {
                    userInfo.yjscPrintersInfo = orgInfo;
                }
            }
            return callback(queryErr, userInfo);
        });
    });
};


/**
 * 通过名字查询用户id
 * @param name  用户名称数组
 * @return  callback  用户id数组
 *
 */
ItbUserModel.prototype.getUserIdByName = function(name,callback){
    var THIS = this;
    var tableName = this.getTableName();
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql ="SELECT	" +
                "	  iu.user_id	" +
                "	FROM	" +
                "	  itbour.itb_user iu	" +
                "	WHERE iu.del_flg = 0	" ;
        if(name && name.length>0){
            for(var i = 0 ;i<name.length;i++){
                if(i == 0){sql+= " AND iu.user_name = '"+name[i]+"'";}
                if(i > 0){sql+=" OR iu.user_name = '"+name[i]+"'";}
            }
        }
        console.log(sql);
        connection.query(sql, function(err, result) {
            // And done with the connection.
            connection.release();

            if(err) {
                console.log(err);
                callback(err, null);
            } else {
                callback(null, result);
            }
        });
    });
};

// 导出
module.exports = ItbUserModel;